﻿* Encoding: UTF-8.
dataset close all.
*syntax for regional level data.

cd 'C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data'.


*open base file (based on regioncode; manually constructed)'.

GET DATA
  /TYPE=XLSX
  /FILE='Regional data\Regional level data base.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
*EXECUTE.

*add capital.

compute capitalregion = 0.
if (regioncode = 101) capitalregion = 1.
if (regioncode = 203) capitalregion = 1.
if (regioncode = 301) capitalregion = 1.
if (regioncode = 402) capitalregion = 1.
if (regioncode = 501) capitalregion = 1.
if (regioncode = 601) capitalregion = 1.
if (regioncode = 703) capitalregion = 1.
if (regioncode = 804) capitalregion = 1.
if (regioncode = 903) capitalregion = 1.
if (regioncode = 1002) capitalregion = 1.
if (regioncode = 1102) capitalregion = 1.
if (regioncode = 1203) capitalregion = 1.
if (regioncode = 1303) capitalregion = 1.
if (regioncode = 1401) capitalregion = 1.
if (regioncode = 1505) capitalregion = 1.
value labels capitalregion 0 'country capital not in this region' 1 'country capital not in this region' . 
fre capitalregion.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\Regional level data base.sav'
  /COMPRESSED.

*GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Regional level data base.sav'.
*dataset name base.


*cro country by qog_samelevel.
* fre qog_samelevel.

*add variables. 

******Prepare QOG.
*Quality of government. 

GET DATA
  /TYPE=XLSX
  /FILE='Regional data\Original files\QOG NUTS.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
EXECUTE.
dataset name qog.

*only select relevant countries.
*fre nuts0.
select if (nuts0 eq 'AT' OR nuts0 eq 'BE' OR nuts0 eq 'BG' OR nuts0 eq 'CZ' OR nuts0 eq 'DE' OR nuts0 eq 'DK' OR nuts0 eq 'ES'
    OR nuts0 eq 'FI' OR nuts0 eq 'FR' OR nuts0 eq 'EL' OR nuts0 eq 'IE' OR nuts0 eq 'IT' OR nuts0 eq 'NL' OR nuts0 eq 'PL' OR nuts0 eq 'UK') .

*fre nuts0.

CASESTOVARS
  /ID = region_code
  /INDEX=year
  /GROUPBY=VARIABLE.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG total.sav'
  /COMPRESSED.

dataset close all.
GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG total.sav'.
dataset name qogtotal.

*select countries with correct data (to merge). 
select if (nuts0 eq 'BE'  OR nuts0 eq 'CZ' OR nuts0 eq 'DE' OR nuts0 eq 'DK' 
    OR nuts0 eq 'FI' OR nuts0 eq 'FR' OR nuts0 eq 'EL' OR nuts0 eq 'IE' OR nuts0 eq 'PL' OR nuts0 eq 'UK') .
fre nuts0.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG same level.sav'
  /COMPRESSED.

dataset close all.
GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG total.sav'.
dataset name qogtotal.

*select countries not the same level.
*AT, BG, ES, IT, NL.
*all countries have NUTS1 in our data, NUTS2 in QOG -> calculate mean.
select if (nuts0 eq 'AT' OR nuts0 eq 'BG' OR nuts0 eq 'ES' OR nuts0 eq 'IT' OR nuts0 eq 'NL') .

fre nuts0 .
fre region_code.

DATASET ACTIVATE qogtotal.
DATASET DECLARE nuts1qoc.
SORT CASES BY nuts1.
AGGREGATE
  /OUTFILE='nuts1qoc'
  /PRESORTED
  /BREAK=nuts1
  /EQI.2010=MEAN(EQI.2010) 
  /EQI.2013 =MEAN(EQI.2013) 
  /EQI.2017 =MEAN(EQI.2017) 
  /EQI.2021 =MEAN(EQI.2021) 
  /qualityp.2010 =MEAN(qualityp.2010) 
  /qualityp.2013 =MEAN(qualityp.2013) 
  /qualityp.2017 =MEAN(qualityp.2017) 
  /qualityp.2021 =MEAN(qualityp.2021) 
  /impartialityp.2010 =MEAN(impartialityp.2010) 
  /impartialityp.2013 =MEAN(impartialityp.2013) 
  /impartialityp.2017 =MEAN(impartialityp.2017) 
  /impartialityp.2021 =MEAN(impartialityp.2021) 
  /corruptionp.2010 =MEAN(corruptionp.2010) 
  /corruptionp.2013 =MEAN(corruptionp.2013) 
  /corruptionp.2017 =MEAN(corruptionp.2017) 
  /corruptionp.2021 =MEAN(corruptionp.2021) 
  /corruption_subExp.2010 =MEAN(corruption_subExp.2010) 
  /corruption_subExp.2013 =MEAN(corruption_subExp.2013) 
  /corruption_subExp.2017 =MEAN(corruption_subExp.2017) 
  /corruption_subExp.2021 =MEAN(corruption_subExp.2021) 
  /corruption_subPer.2010 =MEAN(corruption_subPer.2010) 
  /corruption_subPer.2013 =MEAN(corruption_subPer.2013) 
  /corruption_subPer.2017 =MEAN(corruption_subPer.2017) 
  /corruption_subPer.2021 =MEAN(corruption_subPer.2021).

dataset activate nuts1qoc.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG different level.sav'
  /COMPRESSED.



*****merge QOC files. 

dataset close all.


GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG same level.sav'.
dataset name qogsame.
rename variables region_code = nutscode.
*fre nutscode.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG different level.sav'.
dataset name qogdif.
rename variables nuts1 = nutscode.
alter type nutscode (A4).

*fre nutscode.

dataset activate qogsame.
ADD FILES /FILE=*
  /RENAME (cname EQI_high_me.2010 EQI_high_me.2013 EQI_high_me.2017 EQI_high_me.2021 
    EQI_low_me.2010 EQI_low_me.2013 EQI_low_me.2017 EQI_low_me.2021 EQIregion name nuts0 nuts1 nuts2 
    nuts_level=d0 d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13 d14)
  /FILE='qogdif'
  /DROP=d0 d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13 d14.
EXECUTE.

compute regioncode = -99.
*fre regioncode.


if (nutscode = 'AT1') regioncode  = 101.
if (nutscode = 'AT2') regioncode  = 102.
if (nutscode = 'AT3') regioncode  = 103.
if (nutscode = 'UKF') regioncode  = 201.
if (nutscode = 'UKH') regioncode  = 202.
if (nutscode = 'UKI') regioncode  = 203.
if (nutscode = 'UKC') regioncode  = 204.
if (nutscode = 'UKD') regioncode  = 205.
if (nutscode = 'UKN') regioncode  = 206.
if (nutscode = 'UKM') regioncode  = 207.
if (nutscode = 'UKJ') regioncode  = 208.
if (nutscode = 'UKK') regioncode  = 209.
if (nutscode = 'UKL') regioncode  = 210.
if (nutscode = 'UKG') regioncode  = 211.
if (nutscode = 'UKE') regioncode  = 212.
if (nutscode = 'BE1') regioncode  = 301.
if (nutscode = 'BE2') regioncode  = 302.
if (nutscode = 'BE3') regioncode  = 303.
if (nutscode = 'BG3') regioncode  = 401.
if (nutscode = 'BG4') regioncode  = 402.
if (nutscode = 'CZ06') regioncode  = 501.
if (nutscode = 'CZ03') regioncode  = 502.
if (nutscode = 'CZ08') regioncode  = 503.
if (nutscode = 'CZ01') regioncode  = 504.
if (nutscode = 'CZ05') regioncode  = 505.
if (nutscode = 'CZ04') regioncode  = 506.
if (nutscode = 'CZ02') regioncode  = 507.
if (nutscode = 'CZ07') regioncode  = 508.
if (nutscode = 'DK01') regioncode  = 601.
if (nutscode = 'DK04') regioncode  = 602.
if (nutscode = 'DK05') regioncode  = 603.
if (nutscode = 'DK02') regioncode  = 604.
if (nutscode = 'DK03') regioncode  = 605.
if (nutscode = 'FI20') regioncode  = 701.
if (nutscode = 'FI1C') regioncode  = 702.
if (nutscode = 'FI1B') regioncode  = 703.
if (nutscode = 'FI19') regioncode  = 704.
if (nutscode = 'FI1D') regioncode  = 705.
if (nutscode = 'DE1') regioncode  = 901.
if (nutscode = 'DE2') regioncode  = 902.
if (nutscode = 'DE3') regioncode  = 903.
if (nutscode = 'DE4') regioncode  = 904.
if (nutscode = 'DE5') regioncode  = 905.
if (nutscode = 'DE6') regioncode  = 906.
if (nutscode = 'DE7') regioncode  = 907.
if (nutscode = 'DE8') regioncode  = 908.
if (nutscode = 'DE9') regioncode  = 909.
if (nutscode = 'DEA') regioncode  = 910.
if (nutscode = 'DEB') regioncode  = 911.
if (nutscode = 'DEC') regioncode  = 912.
if (nutscode = 'DED') regioncode  = 913.
if (nutscode = 'DEE') regioncode  = 914.
if (nutscode = 'DEF') regioncode  = 915.
if (nutscode = 'DEG') regioncode  = 916.
if (nutscode = 'EL51') regioncode  = 1001.
if (nutscode = 'EL30') regioncode  = 1002.
if (nutscode = 'EL53') regioncode  = 1003.
if (nutscode = 'EL54') regioncode  = 1004.
if (nutscode = 'EL61') regioncode  = 1005.
if (nutscode = 'EL62') regioncode  = 1006.
if (nutscode = 'EL52') regioncode  = 1007.
if (nutscode = 'EL43') regioncode  = 1008.
if (nutscode = 'EL41') regioncode  = 1009.
if (nutscode = 'EL63') regioncode  = 1010.
if (nutscode = 'EL42') regioncode  = 1011.
if (nutscode = 'EL65') regioncode  = 1012.
if (nutscode = 'EL64') regioncode  = 1013.
if (nutscode = 'ITF') regioncode  = 1201.
if (nutscode = 'ITH') regioncode  = 1202.
if (nutscode = 'ITI') regioncode  = 1203.
if (nutscode = 'ITG') regioncode  = 1204.
if (nutscode = 'ITC') regioncode  = 1205.
if (nutscode = 'NL1') regioncode  = 1301.
if (nutscode = 'NL2') regioncode  = 1302.
if (nutscode = 'NL3') regioncode  = 1303.
if (nutscode = 'NL4') regioncode  = 1304.
if (nutscode = 'ES6') regioncode  = 1501.
if (nutscode = 'ES7') regioncode  = 1502.
if (nutscode = 'ES4') regioncode  = 1503.
if (nutscode = 'ES5') regioncode  = 1504.
if (nutscode = 'ES3') regioncode  = 1505.
if (nutscode = 'ES1') regioncode  = 1506.
if (nutscode = 'ES2') regioncode  = 1507.

recode regioncode (-99=sysmis) (else=copy).
fre regioncode.
alter type regioncode (F4.0).

*calculate national level.
recode regioncode 
(100 thru 120 = 1) 
(200 thru 220 = 2) 
(300 thru 320 = 3) 
(400 thru 420 = 4) 
(500 thru 520 = 5) 
(600 thru 620 = 6) 
(700 thru 720 = 7) 
(800 thru 820 = 8) 
(900 thru 920 = 9) 
(1000 thru 1020 = 10) 
(1100 thru 1120 = 11) 
(1200 thru 1220 = 12) 
(1300 thru 1320 = 13) 
(1400 thru 1420 = 14) 
(1500 thru 1520 = 15) (else=-99)
into countrycode.
*fre countrycode.
AGGREGATE
  /OUTFILE=* MODE=ADDVARIABLES
  /BREAK=countrycode
  /EQI.2010_country=MEAN(EQI.2010) 
  /EQI.2013_country=MEAN(EQI.2013) 
  /EQI.2017_country=MEAN(EQI.2017) 
  /EQI.2021_country=MEAN(EQI.2021).

if  (countrycode = -99) EQI.2010_country = -99.
if  (countrycode = -99) EQI.2013_country = -99.
if  (countrycode = -99) EQI.2017_country = -99.
if  (countrycode = -99) EQI.2021_country = -99.

recode countrycode  EQI.2010_country  EQI.2013_country  EQI.2017_country EQI.2021_country (-99=sysmis) (else=copy).

*fre countrycode .
*fre EQI.2021_country.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG merge.sav'
  /COMPRESSED.

***eurostat file. 

dataset close all.
GET DATA
  /TYPE=XLSX
  /FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Eurostat data.xlsx'
  /SHEET=name 'merge'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
EXECUTE.

compute regioncode = -99.
*fre regioncode.


if (nutscode = 'AT1') regioncode  = 101.
if (nutscode = 'AT2') regioncode  = 102.
if (nutscode = 'AT3') regioncode  = 103.
if (nutscode = 'UKF') regioncode  = 201.
if (nutscode = 'UKH') regioncode  = 202.
if (nutscode = 'UKI') regioncode  = 203.
if (nutscode = 'UKC') regioncode  = 204.
if (nutscode = 'UKD') regioncode  = 205.
if (nutscode = 'UKN') regioncode  = 206.
if (nutscode = 'UKM') regioncode  = 207.
if (nutscode = 'UKJ') regioncode  = 208.
if (nutscode = 'UKK') regioncode  = 209.
if (nutscode = 'UKL') regioncode  = 210.
if (nutscode = 'UKG') regioncode  = 211.
if (nutscode = 'UKE') regioncode  = 212.
if (nutscode = 'BE1') regioncode  = 301.
if (nutscode = 'BE2') regioncode  = 302.
if (nutscode = 'BE3') regioncode  = 303.
if (nutscode = 'BG3') regioncode  = 401.
if (nutscode = 'BG4') regioncode  = 402.
if (nutscode = 'CZ06') regioncode  = 501.
if (nutscode = 'CZ03') regioncode  = 502.
if (nutscode = 'CZ08') regioncode  = 503.
if (nutscode = 'CZ01') regioncode  = 504.
if (nutscode = 'CZ05') regioncode  = 505.
if (nutscode = 'CZ04') regioncode  = 506.
if (nutscode = 'CZ02') regioncode  = 507.
if (nutscode = 'CZ07') regioncode  = 508.
if (nutscode = 'DK01') regioncode  = 601.
if (nutscode = 'DK04') regioncode  = 602.
if (nutscode = 'DK05') regioncode  = 603.
if (nutscode = 'DK02') regioncode  = 604.
if (nutscode = 'DK03') regioncode  = 605.
if (nutscode = 'FI20') regioncode  = 701.
if (nutscode = 'FI1C') regioncode  = 702.
if (nutscode = 'FI1B') regioncode  = 703.
if (nutscode = 'FI19') regioncode  = 704.
if (nutscode = 'FI1D') regioncode  = 705.
if (nutscode = 'DE1') regioncode  = 901.
if (nutscode = 'DE2') regioncode  = 902.
if (nutscode = 'DE3') regioncode  = 903.
if (nutscode = 'DE4') regioncode  = 904.
if (nutscode = 'DE5') regioncode  = 905.
if (nutscode = 'DE6') regioncode  = 906.
if (nutscode = 'DE7') regioncode  = 907.
if (nutscode = 'DE8') regioncode  = 908.
if (nutscode = 'DE9') regioncode  = 909.
if (nutscode = 'DEA') regioncode  = 910.
if (nutscode = 'DEB') regioncode  = 911.
if (nutscode = 'DEC') regioncode  = 912.
if (nutscode = 'DED') regioncode  = 913.
if (nutscode = 'DEE') regioncode  = 914.
if (nutscode = 'DEF') regioncode  = 915.
if (nutscode = 'DEG') regioncode  = 916.
if (nutscode = 'EL51') regioncode  = 1001.
if (nutscode = 'EL30') regioncode  = 1002.
if (nutscode = 'EL53') regioncode  = 1003.
if (nutscode = 'EL54') regioncode  = 1004.
if (nutscode = 'EL61') regioncode  = 1005.
if (nutscode = 'EL62') regioncode  = 1006.
if (nutscode = 'EL52') regioncode  = 1007.
if (nutscode = 'EL43') regioncode  = 1008.
if (nutscode = 'EL41') regioncode  = 1009.
if (nutscode = 'EL63') regioncode  = 1010.
if (nutscode = 'EL42') regioncode  = 1011.
if (nutscode = 'EL65') regioncode  = 1012.
if (nutscode = 'EL64') regioncode  = 1013.
if (nutscode = 'ITF') regioncode  = 1201.
if (nutscode = 'ITH') regioncode  = 1202.
if (nutscode = 'ITI') regioncode  = 1203.
if (nutscode = 'ITG') regioncode  = 1204.
if (nutscode = 'ITC') regioncode  = 1205.
if (nutscode = 'NL1') regioncode  = 1301.
if (nutscode = 'NL2') regioncode  = 1302.
if (nutscode = 'NL3') regioncode  = 1303.
if (nutscode = 'NL4') regioncode  = 1304.
if (nutscode = 'ES6') regioncode  = 1501.
if (nutscode = 'ES7') regioncode  = 1502.
if (nutscode = 'ES4') regioncode  = 1503.
if (nutscode = 'ES5') regioncode  = 1504.
if (nutscode = 'ES3') regioncode  = 1505.
if (nutscode = 'ES1') regioncode  = 1506.
if (nutscode = 'ES2') regioncode  = 1507.

recode regioncode (-99=sysmis) (else=copy).
*fre regioncode.
alter type regioncode (F4.0).

*select relevant regions only.
Alter type poverty_2011 poverty_2012 poverty_2013 poverty_2014
 poverty_2015 poverty_2016 poverty_2017 poverty_2018 poverty_2019 poverty_2020 
 poverty_2021 poverty_2022 unem_2014 unem_2015 unem_2016 unem_2017 unem_2018 unem_2019 unem_2020 
 unem_2021 unem_2022 econactive_2014 econactive_2015 econactive_2016 econactive_2017 econactive_2018 econactive_2019
  econactive_2020 econactive_2021 econactive_2022 unemrate2_2012 unemrate2_2013 unemrate2_2014 unemrate2_2015 unemrate2_2016
 unemrate2_2017 unemrate2_2018 unemrate2_2019 unemrate2_2020 unemrate2_2021 unemrate2_2022 unemrate2_2023 (F10.2) .

*compute unemrate.

compute unemrate_2014 = (unem_2014 / econactive_2014)*100.
compute unemrate_2015 = (unem_2015 / econactive_2015)*100.
compute unemrate_2016 = (unem_2016 / econactive_2016)*100.
compute unemrate_2017 = (unem_2017 / econactive_2017)*100.
compute unemrate_2018 = (unem_2018 / econactive_2018)*100.
compute unemrate_2019 = (unem_2019 / econactive_2019)*100.
compute unemrate_2020 = (unem_2020 / econactive_2020)*100.
compute unemrate_2021 = (unem_2021 / econactive_2021)*100.
compute unemrate_2022 = (unem_2022 / econactive_2022)*100.


***largest difference  = 0.11 and -0.06: generally okay. (for relevant regions).
*cro unemrate_2021 by unemrate2_2021.
*compute unemdif = unemrate_2021 - unemrate2_2021.
*fre unemdif.


SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\eurostat regional data.sav'
  /COMPRESSED.

*****************UK + FRANCE.
**added on 12-7-2024.

dataset close all.
*open base file (based on regioncode; manually constructed)'.
*France.
GET DATA
  /TYPE=XLSX
  /FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Regional GDP per capita UK France.xlsx'
  /SHEET=name 'GDP France'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
EXECUTE.

*link region to code.
compute regioncode_fr = -99.
if (nutscode = 'FRI1') regioncode_fr = 801.
if (nutscode = 'FRF2') regioncode_fr = 802.
if (nutscode = 'FRY') regioncode_fr = 803.
if (nutscode = 'FR1') regioncode_fr = 804.
if (nutscode = 'FRJ1') regioncode_fr = 805.
if (nutscode = 'FRF3') regioncode_fr = 806.
if (nutscode = 'FRE1') regioncode_fr = 807.
if (nutscode = 'FRG0') regioncode_fr = 808.
if (nutscode = 'FRK') regioncode_fr = 809.
if (nutscode = 'FRJ2') regioncode_fr = 801.
if (nutscode = 'FRE2') regioncode_fr = 802.
if (nutscode = 'FRL0') regioncode_fr = 805.
if (nutscode = 'FRF1') regioncode_fr = 806.
if (nutscode = 'FRH0') regioncode_fr = 808.
if (nutscode = 'FRI2') regioncode_fr = 801.
if (nutscode = 'FRD2') regioncode_fr = 802.
if (nutscode = 'FRM0') regioncode_fr = 805.
if (nutscode = 'FRC2') regioncode_fr = 806.
if (nutscode = 'FRI3') regioncode_fr = 808.

*fre regioncode_fr.
*cro nutslevel by regioncode_fr.

DATASET DECLARE gdp_france.
SORT CASES BY regioncode_fr.
AGGREGATE
  /OUTFILE='gdp_france'
  /PRESORTED
  /BREAK=regioncode_fr
  /gdp_2011=MEAN(gdp_2011_old) 
  /gdp_2012=MEAN(gdp_2012_old) 
  /gdp_2013=MEAN(gdp_2013_old) 
  /gdp_2014=MEAN(gdp_2014_old) 
  /gdp_2015=MEAN(gdp_2015_old) 
  /gdp_2016=MEAN(gdp_2016_old) 
  /gdp_2017=MEAN(gdp_2017_old) 
  /gdp_2018=MEAN(gdp_2018_old) 
  /gdp_2019=MEAN(gdp_2019_old) 
  /gdp_2020=MEAN(gdp_2020_old) 
  /gdp_2021=MEAN(gdp_2021_old) 
  /gdp_2022=MEAN(gdp_2022_old).

dataset activate gdp_france.

select if regioncode_fr ne -99.
compute regioncode=regioncode_fr.


SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\GDP France.sav'
  /COMPRESSED.

*UK.
dataset close all.
GET DATA
  /TYPE=XLSX
  /FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Regional GDP per capita UK France.xlsx'
  /SHEET=name 'GDP UK'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
EXECUTE.

*pounds to euros.
*source: https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/eurofxref-graph-gbp.en.html

*1.1633.
compute gdp_2011_uk = gdp_2011_pound * 1.1522.
compute gdp_2012_uk = gdp_2012_pound * 1.2253.
compute gdp_2013_uk = gdp_2013_pound * 1.1995.
compute gdp_2014_uk = gdp_2014_pound * 1.2839.
compute gdp_2015_uk = gdp_2015_pound * 1.3625.
compute gdp_2016_uk = gdp_2016_pound * 1.2203.
compute gdp_2017_uk = gdp_2017_pound * 1.1407.
compute gdp_2018_uk = gdp_2018_pound * 1.1303.
compute gdp_2019_uk = gdp_2019_pound * 1.1393.
compute gdp_2020_uk = gdp_2020_pound * 1.124.
compute gdp_2021_uk = gdp_2021_pound * 1.1633.
compute gdp_2022_uk = gdp_2022_pound * 1.1727.

if (ITLcode = 'TLF') regioncode = 201.
if (ITLcode = 'TLH') regioncode = 202.
if (ITLcode = 'TLI') regioncode = 203.
if (ITLcode = 'TLC') regioncode = 204.
if (ITLcode = 'TLD') regioncode = 205.
if (ITLcode = 'TLN') regioncode = 206.
if (ITLcode = 'TLM') regioncode = 207.
if (ITLcode = 'TLJ') regioncode = 208.
if (ITLcode = 'TLK') regioncode = 209.
if (ITLcode = 'TLL') regioncode = 210.
if (ITLcode = 'TLG') regioncode = 211.
if (ITLcode = 'TLE') regioncode = 212.

alter type  gdp_2011_uk gdp_2012_uk gdp_2013_uk gdp_2014_uk gdp_2015_uk gdp_2016_uk
   gdp_2017_uk gdp_2018_uk gdp_2019_uk gdp_2020_uk gdp_2021_uk gdp_2022_uk (F5.0).

compute country = 2.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\GDP UK.sav'
  /keep regioncode regionname country gdp_2011_uk gdp_2012_uk gdp_2013_uk gdp_2014_uk gdp_2015_uk gdp_2016_uk
   gdp_2017_uk gdp_2018_uk gdp_2019_uk gdp_2020_uk gdp_2021_uk gdp_2022_uk
  /COMPRESSED.

**merge eurostat.

dataset close all.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\eurostat regional data.sav'.
dataset name eurostat.

*first france: new cases, based on new classification.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\GDP France.sav'.
dataset name gdp_france.

dataset activate eurostat. 
ADD FILES /FILE=*
  /FILE='gdp_france'
  /RENAME (regioncode_fr=d0)
  /DROP=d0.
EXECUTE.

*fre regioncode.

dataset close gdp_france.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\GDP UK.sav'.
dataset name gdp_uk.
sort cases by regioncode.
DATASET ACTIVATE eurostat.
sort cases by regioncode.
MATCH FILES /FILE=*
  /TABLE='gdp_uk'
  /BY regioncode.
EXECUTE.

dataset close gdp_uk.
*gdp uk to gdp variable.
*fre country.
if (country = 2) gdp_2011 = gdp_2011_uk.
if (country = 2) gdp_2012 = gdp_2012_uk.
if (country = 2) gdp_2013 = gdp_2013_uk.
if (country = 2) gdp_2014 = gdp_2014_uk.
if (country = 2) gdp_2015 = gdp_2015_uk.
if (country = 2) gdp_2016 = gdp_2016_uk.
if (country = 2) gdp_2017 = gdp_2017_uk.
if (country = 2) gdp_2018 = gdp_2018_uk.
if (country = 2) gdp_2019 = gdp_2019_uk.
if (country = 2) gdp_2020 = gdp_2020_uk.
if (country = 2) gdp_2021 = gdp_2021_uk.
if (country = 2) gdp_2022 = gdp_2022_uk.
*fre gdp_2021_uk gdp_2021.

select if not missing(regioncode).
sort cases by regioncode.

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\eurostat regional data full.sav'
  /drop country regionname gdp_2011_uk gdp_2012_uk gdp_2013_uk gdp_2014_uk gdp_2015_uk gdp_2016_uk gdp_2017_uk gdp_2018_uk gdp_2019_uk gdp_2020_uk gdp_2021_uk gdp_2022_uk
  /COMPRESSED.

*****merge files. 

dataset close all.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Regional level data base.sav'.
dataset name base.
*fre nutscode.
sort cases by regioncode.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\QOG merge.sav'.
dataset name qogmerge.

select if not missing(regioncode).
sort cases by regioncode.

DATASET ACTIVATE base.
MATCH FILES /FILE=*
  /TABLE='qogmerge'
  /BY regioncode.
EXECUTE.


dataset close qogmerge.

GET
  FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\eurostat regional data full.sav'.
dataset name eurostat.


DATASET ACTIVATE base.
MATCH FILES /FILE=*
  /TABLE='eurostat'
  /BY regioncode.
EXECUTE.


SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\Regional data.sav'
  /COMPRESSED.


SAVE TRANSLATE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\Regional data full.xlsx'
  /TYPE=XLS
  /VERSION=12
  /MAP
  /FIELDNAMES VALUE=NAMES
  /CELLS=VALUES
  /REPLACE.



**********************************
    dataset close all.

*national data.

GET DATA
  /TYPE=XLSX
  /FILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional '+
    'data\Eurostat data.xlsx'
  /SHEET=name 'country'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.
EXECUTE.

Alter type povertycountry_2011 povertycountry_2012 povertycountry_2013 povertycountry_2014 povertycountry_2015 povertycountry_2016
     povertycountry_2017 povertycountry_2018 povertycountry_2019 povertycountry_2020 povertycountry_2021 povertycountry_2022 unemcountry_2014
      unemcountry_2015 unemcountry_2016 unemcountry_2017 unemcountry_2018 unemcountry_2019 unemcountry_2020 unemcountry_2021 unemcountry_2022
       econactivecountry_2014 econactivecountry_2015 econactivecountry_2016 econactivecountry_2017 econactivecountry_2018 econactivecountry_2019 
       econactivecountry_2020 econactivecountry_2021 econactivecountry_2022 unemratecountry_2012 unemratecountry_2013 
       unemratecountry_2014 unemratecountry_2015 unemratecountry_2016 unemratecountry_2017 unemratecountry_2018 
       unemratecountry_2019 unemratecountry_2020 unemratecountry_2021 unemratecountry_2022 (F10.2).

SAVE OUTFILE='C:\Users\tsipma\surfdrive\Wetenschappelijke artikelen\Emilien-David-Take\Data\Regional data\National data.sav'
  /COMPRESSED.





